﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Collections;
using System.Collections.ObjectModel;
using SYcwpb.Helpers;
using System.Data;
using System.Data.SqlClient;
using SYcwpb.ViewModels.DataProvider;

namespace SYcwpb.Access
{
    class BedRoomsAccess
    {
        private int lineid = Convert.ToInt32(CONSTDATA.UserModel.Line);
        private SqlDataLayer sqlHelper = new SqlDataLayer();

        public DataTable GetRooms()
        {
            string sql_getroom = "select * from BedRooms where br_line = " + lineid + " order by br_sex, br_priority,br_name";

            return sqlHelper.SelectDt(sql_getroom);
        }

        /// <summary>
        /// 取出今天夜班的司机，并将次日早班出勤时间按照赋予司机，等于执行一次仅夜班司机的安排交路到司机
        /// 注：尚未完善，缺少请假调班司机的处理！！！
        /// </summary>
        /// <returns></returns>
        public DataTable GetYBDrivers(int tableID)
        {
            //找出正线的司机
          /*  string sql_getDrvier = "select" +
                            " '' as DutyTime,"+
                            " Dgp.dgrp_dcom_id as dcom_id,"+
                            " S.user_view_name as userName," +
                            " S.user_sex as sex" + 
                            " from DriverGroup Dgp left join DriverCombine Dcom on Dgp.dgrp_dcom_id = Dcom.ID" +
                            " left join ClassTurn Ctn on Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line" +
                            " left join [system_user] S on Dgp.dgrp_user_id = S.user_id" +
                            " where Dcom.dcom_lineid = " + lineid + " and Ctn.ctn_cls_id = 3 and Dcom.dcom_grp_type = 5  order by Dcom.dcom_ybarrive,Dcom.ID";
            DataTable driverDT = sqlHelper.SelectDt(sql_getDrvier);

            //司机所在分组
            string sql_getDcom = "select Dcom.ID as dcom_id,'' as DutyTime"+
                                " from DriverCombine Dcom left join ClassTurn Ctn" +
                                " on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)" +
                                " where Dcom.dcom_lineid = " + lineid + " and Ctn.ctn_cls_id = 3 and dcom_date='"+GetData()+"' and Dcom.dcom_grp_type = 5" +
                                " order by Dcom.dcom_ybarrive, Dcom.dcom_dutynum ";
            DataTable combineDT = sqlHelper.SelectDt(sql_getDcom);

            //找出正线的交路
            string crossRoadZB = "select attenTime from CrossingRoad where tableInfo_id = " + tableID + " and zby = 0 and isused = 1 and grp_id = 5 order by number";
            DataTable roadsZB = sqlHelper.SelectDt(crossRoadZB);

            //将交路的AttenTime 赋予CombineDT
            int count = roadsZB.Rows.Count >= combineDT.Rows.Count ? combineDT.Rows.Count : roadsZB.Rows.Count;   //返回行数少的
            for (int i = 0; i < count; i++)
            {
                combineDT.Rows[i]["DutyTime"] = roadsZB.Rows[i]["attenTime"].ToString();
            }

            //正线司机的出勤时间加载
            string dcom_id;
            for (int k = 0 ; k < driverDT.Rows.Count; k ++)
            {
                dcom_id = driverDT.Rows[k]["dcom_id"].ToString();
                for (int index = 0; index < combineDT.Rows.Count; index++)
                {
                    if (dcom_id == combineDT.Rows[index]["dcom_id"].ToString())
                    {
                        driverDT.Rows[k]["DutyTime"] = combineDT.Rows[index]["DutyTime"].ToString();
                        break;
                    }
                }
            }

            //非正线司机的时间安排
            string sql_specialDriver = "select"+
                                    " S.user_view_name as userName,"+
                                    " Dcom.ID as dcom_id,"+
                                    " S.user_sex as sex,"+
                                    " Cr.attenTime as DutyTime" +
                                    " from DriverGroup Dgp left join DriverCombine Dcom on Dgp.dgrp_dcom_id = Dcom.ID"+
                                    " left join ClassTurn Ctn on Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line"+
                                    " left join CrossingRoad Cr on Dcom.dcom_grp_type = Cr.grp_id "+
                                    " left join [system_user] S on Dgp.dgrp_user_id = S.user_id" +
                                    " where tableInfo_id = 58 and isused = 1 and Dcom.dcom_lineid = 2 and Dcom.dcom_grp_type <> 5 and Ctn.ctn_cls_id = 3 and Cr.zby = 0";
            DataTable specialDriverDT = sqlHelper.SelectDt(sql_specialDriver);

            for (int i = 0; i < specialDriverDT.Rows.Count; i++)
            {
                DataRow row = driverDT.NewRow();
                row["DutyTime"] = specialDriverDT.Rows[i]["DutyTime"].ToString();
                row["dcom_id"] = specialDriverDT.Rows[i]["dcom_id"].ToString();
                row["userName"] = specialDriverDT.Rows[i]["userName"].ToString();
                row["sex"] = specialDriverDT.Rows[i]["sex"].ToString();

                driverDT.Rows.Add(row);
            }*/
            string sql_getDrvier = "select D.dcdr_crossAttenTime DutyTime,d.dcdr_driverCombineId dcom_id,S.user_view_name userName,s.user_sex sex,TBL .Total from DriverCombineCrossingRoadPlane D left join [system_user] S on (D.replaceDriver is null and D.dcdr_userID = S.user_id) or  D.replaceDriver=S.user_id left join (select dc.dcdr_driverCombineId, COUNT(*) Total from DriverCombineCrossingRoadPlane dc where dc.dcdr_cls_id='0' and dc.line='" + lineid + "' and dc.dcdr_date='" + DateTime.Now.AddDays(1).ToShortDateString() + "' group by dc.dcdr_driverCombineId)TBL on TBL.dcdr_driverCombineId=d.dcdr_driverCombineId where dcdr_cls_id='0' and D.line='" + lineid + "' and dcdr_driverType<>7 and dcdr_date='" + DateTime.Now.AddDays(1).ToShortDateString() + "' order by case when D.dcdr_crossAttenTime is null then 1 else 0 end ,D.dcdr_crossAttenTime asc ";
            DataTable driverDT = sqlHelper.SelectDt(sql_getDrvier);
            return driverDT;
        }


        public DataTable GetRoomMaps(string today)
        {
            string sql_get = "select * from BedRoomMaps where brm_date = '" + today + "' and brm_line = '" + lineid + "'";

            return sqlHelper.SelectDt(sql_get);
        }

        public bool GetTodayBedRoomRecord(string today)
        {
            string sql = "select count(*) from BedRoomRecord where line = " + lineid + " and date = '" + today + "'";

            return Convert.ToInt32(sqlHelper.GetSingle(sql))== 0 ? false : true ;
        }

        public bool UpdateBedRooms(BedRoomObjects beds, bool record, string today)
        {
            if (beds == null || beds.Count < 1)
                return false;

            ArrayList sqlList = new ArrayList();

            //如果今天已经保存有了，先将BedRoomMaps 里今天的记录全部删除
            if (record)
            {
                string dele = "delete from BedRoomMaps where brm_date = '" + today + "' and brm_line = '" + lineid + "'";
                sqlList.Add(dele);
            }

            string oneRow = "";
            foreach (BedRoomObject bed in beds)
            {
                if (bed.TheDriver == null)
                    oneRow = "insert into BedRoomMaps values('" + bed.RoomName + "','" + bed.BedIndex + "',null,null,null,'" + today + "','" + lineid + "')";
                else
                    oneRow = "insert into BedRoomMaps values('" + bed.RoomName + "','" + bed.BedIndex + "','" + bed.TheDriver.DriverName + "','" +
                    bed.TheDriver.DutyTime + "','" + bed.TheDriver.CallTime + "','" + today + "','" + lineid + "')";

                sqlList.Add(oneRow);
            }

            //如果没添加记录，则添加一条
            if (!record)
            {
                string strRecord = "insert into BedRoomRecord values ('" + today + "','" + lineid + "')";
                sqlList.Add(strRecord);
            }

            return sqlHelper.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 取消脚本
        /// </summary>
        /// <param name="str"></param>
        public bool CancelCall(string name,string time)
        {
            ArrayList arr = new ArrayList();
            string str = "UPDATE INFORM SET deleted=1 WHERE CONVERT(VARCHAR(10),Begindate,120)='"+DateTime.Now.ToString("yyyy-MM-dd")+"' AND Callvoice like '%"+name+"%"+time+"%'";
            //MessageBoxHelper.ShowError(str);
            arr.Add(str);
            return sqlHelper.ExecuteSqlTran(arr);
        }
        public DataTable GetInform(DateTime datetime) 
        {
            string str = "select idd,phoneno,begindate,callvoice,uno,deleted from inform where CONVERT(VARCHAR(10),Begindate,120)='" + datetime.ToString("yyyy-MM-dd") + "'";
            return sqlHelper.SelectDt(str);
        }
        public string GetData()
        {
            DateTime dt1 = DateTime.Now;//当前时间
            DateTime dt2 = Convert.ToDateTime(DateTime.Now.ToShortDateString() + " 02:00:00");//更新时间
            if (dt1 > dt2)
            {
                return DateTime.Now.ToString("yyyy-MM-dd");
            }
            else
            {
                return DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
            }
            return DateTime.Now.ToString("yyyy-MM-dd");

        }

    }
}
